package com.minis.jdbc.core;

import com.minis.beans.factory.annotation.Autowired;

import javax.sql.DataSource;
import java.sql.*;
import java.util.List;

/**
 * 传入具体要执行的 SQL 语句，并把返回值的处理逻辑设计成一个模板方法去具体实现
 */
public class JdbcTemplate {
    public JdbcTemplate() {
    }

//    public Object query(String sql){
//        Connection con = null;
//        PreparedStatement stmt = null;
//        ResultSet rs = null;
//        Object rtnObj = null;
//
//
//        try{
//            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//            con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databasename=DEMO;user=sa;password=Sql2016;");
//            stmt = con.prepareStatement(sql);
//            rs = stmt.executeQuery();
//
//            // 调用返回值的处理逻辑，由程序员具体实现
//            rtnObj = doInStatement(rs);
//        } catch (ClassNotFoundException | SQLException e) {
//            e.printStackTrace();
//        } finally {
//            try {
//                rs.close();
//                stmt.close();
//                con.close();
//            } catch (SQLException throwables) {
//                throwables.printStackTrace();
//            }
//        }
//        return rtnObj;
//
//    }
//
//    protected abstract Object doInStatement(ResultSet rs);

    // 通过数据源（配置注入）获取数据库连接，而非硬编码的方式
    private DataSource dataSource;

    // Callback 模式
    public Object query(StatementCallback stmtcallback) {
        Connection con = null;
        Statement stmt = null;

        try {
//            // 硬编码
//            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//            con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databasename=DEMO;user=sa;password=Sql2016;");
            con = dataSource.getConnection();

            stmt = con.createStatement();

            return stmtcallback.doInStatement(stmt);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                stmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
        return null;
    }

    /**
     * //调用过程
     *   public User getUserInfo(int userid) {
     *     @Autowired
     *     JdbcTemplate jdbcTemplate;
     *     final String sql = "select id, name,birthday from users where id="+userid;
     *     return (User)jdbcTemplate.query(
     *         (stmt)->{
     *           ResultSet rs = stmt.executeQuery(sql);
     *           User rtnUser = null;
     *           if (rs.next()) {
     *             rtnUser = new User();
     *             rtnUser.setId(userid);
     *             rtnUser.setName(rs.getString("name"));
     *             rtnUser.setBirthday(new java.util.Date(rs.getDate("birthday").getTime()));
     *           }
     *           return rtnUser;
     *         }
     *     );
     *   }
     */


    // PreparedStatement
    public Object query(String sql, Object[] args, PreparedStatementCallback pstmtcallback) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = dataSource.getConnection();
            pstmt = con.prepareStatement(sql);
            // 通过 argumentSetter 同一设置参数值
            ArgumentPreparedStatementSetter argumentSetter = new ArgumentPreparedStatementSetter(args);
            argumentSetter.setValues(pstmt);
            return pstmtcallback.doInPreparedStatement(pstmt);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                pstmt.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return null;
    }


    // 返回多条数据
    public <T> List<T> query(String sql, Object[] args, RowMapper<T> rowMapper) {
        RowMapperResultSetExtractor<T> resultExtractor = new RowMapperResultSetExtractor<>(rowMapper);
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            // 建立数据库连接
            con = dataSource.getConnection();

            // 准备SQL命令语句
            pstmt = con.prepareStatement(sql);
            // 设置参数
            ArgumentPreparedStatementSetter argumentSetter = new ArgumentPreparedStatementSetter(args);
            argumentSetter.setValues(pstmt);
            // 执行语句
            rs = pstmt.executeQuery();

            // 将数据库结果集映射为对象列表，返回
            return resultExtractor.extractData(rs);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                pstmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
        return null;
    }


    /**
     * // 调用过程
     *   public List<User> getUsers(int userid) {
     *     final String sql = "select id, name,birthday from users where id>?";
     *     return (List<User>)jdbcTemplate.query(sql, new Object[]{new Integer(userid)},
     *         new RowMapper<User>(){
     *           public User mapRow(ResultSet rs, int i) throws SQLException {
     *             User rtnUser = new User();
     *             rtnUser.setId(rs.getInt("id"));
     *             rtnUser.setName(rs.getString("name"));
     *             rtnUser.setBirthday(new java.util.Date(rs.getDate("birthday").getTime()));
     *
     *             return rtnUser;
     *           }
     *         }
     *     );
     *   }
     */
}
